package Sungjuk;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class SungJukDAO implements ISungJukDAO {
	private String DRV = "oracle.jdbc.driver.OracleDriver";
	private String URL = "jdbc:oracle:thin:untiring07/untiring07@112.216.253.125:1521:xe";
	private String insertSQL = "insert into EX01SungJuk values(exam.nextval,?,?,?,?,?,?,?)";
	private String selectSQL = "select*from EX01SungJuk order by sjno desc";
	private String updateSQL = "update EX01SungJuk set kor =?,eng = ?,mat =?,tot = ?,avg = ?,gra = ? where name = ?";
	private String deleteSQL = "delete EX01SungJuk where mno = ?";
	@Override
	public boolean insertSungJuk(SungjukVO s) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
	
		try{
			conn = this.openDatabase();
			pstmt = conn.prepareStatement(insertSQL);
			pstmt.setString(1, s.getName());
			pstmt.setInt(2, s.getKor());
			pstmt.setInt(3, s.getEng());
			pstmt.setInt(4, s.getMat());
			pstmt.setInt(5, s.getTot());
			pstmt.setFloat(6, s.getAvg());
			pstmt.setString(7, s.getGra());
			pstmt.executeUpdate();
			result = true;
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeDatabase(conn, pstmt, null);
		}
		
		return result;
	}
	private Connection openDatabase(){
		try{
			Class.forName(DRV);
			return DriverManager.getConnection(URL);
		}catch(Exception e){
			e.printStackTrace();
		}
		return null;
	}
	private void closeDatabase(Connection c, PreparedStatement p , ResultSet r){
		if(c != null)try{c.close(); c = null;}catch(Exception e){}
		if(p != null)try{p.close(); p = null;}catch(Exception e){}
		if(r != null)try{r.close(); r = null;}catch(Exception e){}
		
	}
	@Override
	public List<SungjukVO> selectSungJuk() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<SungjukVO> result = new ArrayList<>();
		try{
			conn = this.openDatabase();
			pstmt = conn.prepareStatement(selectSQL);
			rs = pstmt.executeQuery();
			while(rs.next()){
				SungjukVO sj = new SungjukVO();
				sj.setSjno(rs.getInt("sjno"));
				sj.setName(rs.getString("name"));
				sj.setKor(rs.getInt("kor"));
				sj.setEng(rs.getInt("eng"));
				sj.setMat(rs.getInt("mat"));
				sj.setTot(rs.getInt("tot"));
				sj.setAvg(rs.getFloat("avg"));
				sj.setGra(rs.getString("gra"));
				result.add(sj);
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeDatabase(conn, pstmt, null);
		}
		return result;
	}
	@Override
	public boolean updateSungJuk(SungjukVO sjvo) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		try{
			conn = this.openDatabase();
			pstmt = conn.prepareStatement(updateSQL);
			pstmt.setInt(1,sjvo.getKor());
			pstmt.setInt(2,sjvo.getEng());
			pstmt.setInt(3,sjvo.getMat());
			pstmt.setInt(4,sjvo.getTot());
			pstmt.setFloat(5,sjvo.getAvg());
			pstmt.setString(6,sjvo.getGra());
			pstmt.setString(7,sjvo.getName());
			
			int sjup = pstmt.executeUpdate();
			if(sjup == 0) System.out.println("변경된 데이터가 없습니다.");
			result = true;
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeDatabase(conn, pstmt, null);
		}
		return result;
	}
	@Override
	public boolean deleteSungJuk(SungjukVO sjvo) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		
		
		
		return false;
	}
	
}
